package MySQL.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @Author wangzhipeng
 * @Date 2020-10-11 8:48 下午
 * @Description C3P0的演示
 *  与DButils 增删改查的案例
 */
public class C3P0DDemo1 {
    public static void main(String[] args) throws SQLException {
        // 1 创建数据库连接池对象
        ComboPooledDataSource source = new ComboPooledDataSource();
        // 2 获取连接对象
        Connection connection = source.getConnection();
        // 3 打印
        System.out.println(connection);

        //1.创建queryrunner对象
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT * FROM user";
        //调用queryrunner的query查询方法
        Object[] result = queryRunner.query(connection, sql, new ArrayHandler()); //读取了一条数据
        for(Object obj:result) {
            System.out.print(obj + " ");
        }
        connection.close();
    }

    /**
     * 更新一条语句
     * @throws SQLException
     */
    @Test
    public void update() throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection connection = dataSource.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "update user set upassword = ?";
        Object[] params = {"123456"};
        int update = queryRunner.update(connection, sql, params); // 更新成功返回1
        System.out.println(update);
        dataSource.close();

    }

    /**
     *插入一条语句
     */
    @Test
    public void insert() throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection connection = dataSource.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "insert into user(uname,upassword) values(?,?)";
        Object[] objects = {"wzp啊","321"};
        int update = queryRunner.update(connection, sql, objects);
        System.out.println(update);
        dataSource.close();
    }
    /**
     * 查询多个结果
     */
    @Test
    public void query() throws SQLException{
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection connection = dataSource.getConnection();
        String sql = "select * from user ";
        QueryRunner queryRunner = new QueryRunner();
        List<Object[]> query = queryRunner.query(connection, sql, new ArrayListHandler());
        for(Object[] objects:query){
            for(Object object:objects){
                System.out.print(object+" ");
            }
            System.out.println();
        }
        dataSource.close();
    }

    /**
     * 测试删除一条语句
     * @throws SQLException
     */
    @Test
    public void delete() throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection connection = dataSource.getConnection();
        String sql = "delete from user where uid = ?";
        QueryRunner queryRunner = new QueryRunner();
        Object[] objects = {3};
        int update = queryRunner.update(connection, sql, objects);
        if(update==1)
             System.out.println("删除成功！");
        dataSource.close();
    }
}
